package DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import obj.AUDIO;
import obj.AUDIOVISUELLE;
import obj.Categorie;
import obj.Genre;
import obj.NewOeuvre;
import obj.Oeuvre;
import obj.Support;
import obj.Type;
import obj.VISUELLE;

public class OeuvreDAO extends DA2O<Oeuvre>{

	public boolean create(Oeuvre obj){
		try{
			String query = "INSERT INTO OEUVRE(O_TITRE,O_AUTEUR,O_GENRE,O_TYPE,O_SUPPORT,O_CATEGORIE,O_NB_UTILISATION) VALUES ('"+obj.getTitre()+"','"+obj.getAuteur()+"','"+obj.getGr().getLib()+"','"+obj.getClass().getSimpleName()+"','"+obj.getSp().getLib()+"','"+obj.getCg().getLib()+"',0)";
			PreparedStatement ps = this.connect.prepareStatement(query);
		
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public Oeuvre find(long id){
		Oeuvre oe=null;
		try{
			ResultSet result = this.connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY).executeQuery("SELECT * FROM OEUVRE WHERE O_ID="+id);
			if (result.first()){
				if (result.getString("O_TYPE").equals("AUDIO")){
					oe = new AUDIO(id, result.getString("O_TITRE"), result.getString("O_AUTEUR"), new GenreDAO().find(result.getString("O_GENRE")), new SupportDAO().find(result.getString("O_SUPPORT")),new CategorieDAO().find(result.getString("O_CATEGORIE")),result.getInt("O_NB_UTILISATION"));
				}else if (result.getString("O_TYPE").equals("AUDIOVISUELLE")){
					oe = new AUDIOVISUELLE(id, result.getString("O_TITRE"), result.getString("O_AUTEUR"), new GenreDAO().find(result.getString("O_GENRE")), new SupportDAO().find(result.getString("O_SUPPORT")),new CategorieDAO().find(result.getString("O_CATEGORIE")),result.getInt("O_NB_UTILISATION"));
				}else if (result.getString("O_TYPE").equals("VISUELLE")){
					oe = new VISUELLE(id, result.getString("O_TITRE"), result.getString("O_AUTEUR"), new GenreDAO().find(result.getString("O_GENRE")), new SupportDAO().find(result.getString("O_SUPPORT")),new CategorieDAO().find(result.getString("O_CATEGORIE")),result.getInt("O_NB_UTILISATION"));
				}
			}else{
				Genre g = new Genre("");
				Support sp = new Support();
				sp.setLib("");
				Categorie cg = new Categorie("");
				
				oe= new NewOeuvre(0, "", "", g, sp, cg, 0);
			}
		}catch(SQLException e){
			e.getMessage();
			//e.printStackTrace();
		}
		return oe;
	}
	
	public boolean update(Oeuvre obj){
		try{
			String query = "UPDATE OEUVRE SET O_TITRE='"+obj.getTitre()+"', O_AUTEUR='"+obj.getAuteur()+"', O_GENRE ='"+obj.getGr().getLib()+"', O_TYPE='"+obj.getClass().getSimpleName()+"', O_SUPPORT='"+obj.getSp().getLib()+"', O_CATEGORIE='"+obj.getCg().getLib()+"', O_NB_UTILISATION="+obj.getNbUtilisation()+" WHERE O_ID="+obj.getId();
			
			PreparedStatement ps = this.connect.prepareStatement(query);
		
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}
		return true;
	}
	
	public void delete(Oeuvre obj){
		try{
			String query = "DELETE FROM OEUVRE WHERE O_ID="+obj.getId();
			
			PreparedStatement ps = this.connect.prepareStatement(query);
		
			ps.executeUpdate();
			ps.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	public Vector<Oeuvre> selectParCritere(String ge, String ty, String su){
		Vector<Oeuvre> cl = new Vector<Oeuvre>();
		
		String query = "SELECT * FROM OEUVRE ";
		String condition = "WHERE ";
		
		String var[] = new String[5];
		var[0] = ge;
		var[1] = ty;
		var[2] = su;
		var[3] = "";
		var[4] = "";
		
		String tab[] = new String[3];
		tab[0] = "O_GENRE='"+ge+"'";
		tab[1] = "O_TYPE='"+ty+"'";
		tab[2] = "O_SUPPORT='"+su+"'";
		
		for (int i=0;i<3;i++){
			if (!var[i].equals("")){
				if (!var[i+1].equals("") || !var[i+2].equals("")){
					condition = condition + tab[i] + " AND ";
				}else {
					condition = condition + tab[i];
				}
			}
		}
		
		if (condition.length()!=6){
			query = query + condition;
		}
		try {
			ResultSet rs = this.connect.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE).executeQuery(
					query);
			
			while(rs.next()){
				Type t = new Type(rs.getString("O_TYPE"));
				Genre g = new Genre(rs.getString("O_GENRE"));
				DAO<Support> sd = new SupportDAO();
				Support s = sd.find(rs.getString("O_SUPPORT"));
				Categorie c = new Categorie(rs.getString("O_CATEGORIE"));
				Oeuvre oe = null;
				if(t.getLib().equals("AUDIO")){
					oe = new AUDIO(rs.getLong(1),rs.getString(2),rs.getString(3),g,s,c,rs.getInt("O_NB_UTILISATION"));
				}else if(t.getLib().equals("AUDIOVISUELLE")){
					oe = new AUDIOVISUELLE(rs.getLong(1),rs.getString(2),rs.getString(3),g,s,c,rs.getInt("O_NB_UTILISATION"));
				}else if(t.getLib().equals("VISUELLE")){
					oe = new VISUELLE(rs.getLong(1),rs.getString(2),rs.getString(3),g,s,c,rs.getInt("O_NB_UTILISATION"));
				}
				cl.add(oe);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cl;
	}
	public long returnID(String query){
		long id = 0;
		try{
			Statement st = this.connect.createStatement();
			ResultSet rs = st.executeQuery(query);
			if(rs.next()){
				id = rs.getLong(1);
			}
		}catch(SQLException e){
			e.getStackTrace();
		}
		return id;
	}
}
